import { query } from '../db'
import { formatDateTime } from '@/utils/time'

export interface Post {
  id: number
  title: string
  description: string
  categoryId: number
  tags: string[]
  views: number
  likes: number
  content: string
  status: number
  category?: {
    id: number
    name: string
  }
  created_at: string
  updated_at: string
}

export interface PostRow {
  id: number
  title: string
  description: string
  categoryId: number
  tags: string
  views: number
  likes: number
  content: string
  status: number
  category_name?: string
  created_at: string
  updated_at: string
}

// 分页查询参数接口
export interface PostQueryParams {
  page?: number
  pageSize?: number
  categoryId?: number
  search?: string
  sortBy?: 'created_at' | 'updated_at' | 'views' | 'likes' | 'title'
  sortOrder?: 'ASC' | 'DESC'
  status?: number
}

// 分页结果接口
export interface PostPageResult {
  posts: Post[]
  total: number
  page: number
  pageSize: number
  totalPages: number
  hasNext: boolean
  hasPrev: boolean
}

// 获取所有文章
export async function getAllPosts(): Promise<Post[]> {
  const sql = `
    SELECT p.*, c.name as category_name 
    FROM blog_posts p 
    LEFT JOIN blog_categories c ON p.categoryId = c.id 
    WHERE p.status = 1 
    ORDER BY p.created_at DESC
  `
  const posts = await query(sql) as PostRow[]
  
  return posts.map(post => ({
    ...post,
    created_at: formatDateTime(post.created_at),
    updated_at: formatDateTime(post.updated_at),
    tags: post.tags ? post.tags.split(',') : [],
    category: post.category_name ? {
      id: post.categoryId,
      name: post.category_name
    } : undefined
  }))
}

// 根据ID获取文章
export async function getPostById(id: number): Promise<Post | null> {
  const sql = `
    SELECT p.*, c.name as category_name 
    FROM blog_posts p 
    LEFT JOIN blog_categories c ON p.categoryId = c.id 
    WHERE p.id = ? AND p.status = 1
  `
  const posts = await query(sql, [id]) as PostRow[]
  
  if (posts.length === 0) return null
  
  const post = posts[0]
  return {
    ...post,
    created_at: formatDateTime(post.created_at),
    updated_at: formatDateTime(post.updated_at),
    tags: post.tags ? post.tags.split(',') : [],
    category: post.category_name ? {
      id: post.categoryId,
      name: post.category_name
    } : undefined
  }
}

//模糊查询
export async function getPostByLikeTitle(title: string): Promise<Post[]> {
  const sql = `
    SELECT p.*, c.name as category_name 
    FROM blog_posts p 
    LEFT JOIN blog_categories c ON p.categoryId = c.id 
    WHERE p.title LIKE ? OR p.description LIKE ? OR p.content LIKE ? AND p.status = 1
  `
  const posts = await query(sql, [`%${title}%`, `%${title}%`, `%${title}%`]) as PostRow[]
  
  return posts.map(post => ({
    ...post,
    created_at: formatDateTime(post.created_at),
    updated_at: formatDateTime(post.updated_at),
    tags: post.tags ? post.tags.split(',') : [],
    category: post.category_name ? {
      id: post.categoryId,
      name: post.category_name
    } : undefined
  }))
}

// 分页查询文章
export async function getPostsPage(params: PostQueryParams = {}): Promise<PostPageResult> {
  const {
    page = 1,
    pageSize = 10,
    categoryId,
    search,
    sortBy = 'created_at',
    sortOrder = 'DESC',
    status = 1
  } = params

  // 构建 WHERE 条件
  const conditions: string[] = []
  const queryParams: any[] = []

  if (status !== undefined) {
    conditions.push('p.status = ?')
    queryParams.push(status)
  }

  if (categoryId) {
    conditions.push('p.categoryId = ?')
    queryParams.push(categoryId)
  }

  if (search) {
    conditions.push('(p.title LIKE ? OR p.description LIKE ? OR p.tags LIKE ?)')
    const searchTerm = `%${search}%`
    queryParams.push(searchTerm, searchTerm, searchTerm)
  }

  const whereClause = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : ''

  // 构建排序
  const orderBy = `ORDER BY p.${sortBy} ${sortOrder}`

  // 查询总数
  const countSql = `
    SELECT COUNT(*) as total 
    FROM blog_posts p 
    LEFT JOIN blog_categories c ON p.categoryId = c.id 
    ${whereClause}
  `
  const countResult = await query(countSql, queryParams) as any[]
  const total = countResult[0]?.total || 0

  // 计算分页参数
  const offset = (page - 1) * pageSize
  const totalPages = Math.ceil(total / pageSize)

  // 查询文章列表
  const sql = `
    SELECT p.*, c.name as category_name 
    FROM blog_posts p 
    LEFT JOIN blog_categories c ON p.categoryId = c.id 
    ${whereClause}
    ${orderBy}
    LIMIT ?, ?
  `
  const posts = await query(sql, [...queryParams, offset, pageSize]) as PostRow[]

  // 转换数据格式
  const formattedPosts = posts.map(post => ({
    ...post,
    created_at: formatDateTime(post.created_at),
    updated_at: formatDateTime(post.updated_at),
    tags: post.tags ? post.tags.split(',') : [],
    category: post.category_name ? {
      id: post.categoryId,
      name: post.category_name
    } : undefined
  }))

  return {
    posts: formattedPosts,
    total,
    page,
    pageSize,
    totalPages,
    hasNext: page < totalPages,
    hasPrev: page > 1
  }
}

// 搜索文章
export async function searchPosts(searchQuery: string): Promise<Post[]> {
  const sql = `
    SELECT p.*, c.name as category_name 
    FROM blog_posts p 
    LEFT JOIN blog_categories c ON p.categoryId = c.id 
    WHERE p.status = 1 
    AND (p.title LIKE ? OR p.description LIKE ? OR p.tags LIKE ?)
    ORDER BY p.created_at DESC
  `
  const searchTerm = `%${searchQuery}%`
  const posts = await query(sql, [searchTerm, searchTerm, searchTerm]) as PostRow[]
  
  return posts.map(post => ({
    ...post,
    tags: post.tags ? post.tags.split(',') : [],
    category: post.category_name ? {
      id: post.categoryId,
      name: post.category_name
    } : undefined
  }))
}

// 增加文章浏览量
export async function incrementViews(postId: number): Promise<void> {
  const sql = 'UPDATE blog_posts SET views = views + 1 WHERE id = ?'
  await query(sql, [postId])
}

// 增加文章点赞数
export async function incrementLikes(postId: number): Promise<void> {
  const sql = 'UPDATE blog_posts SET likes = likes + 1 WHERE id = ?'
  await query(sql, [postId])
}

// 获取浏览量
export async function getViews(postId: number): Promise<number> {
  const sql = 'SELECT views FROM blog_posts WHERE id = ?'
  const result = await query(sql, [postId]) as any[]
  if (!result || !result[0]) {
    return 0
  }
  return result[0].views
}

// 获取点赞数
export async function getLikes(postId: number): Promise<number> {
  const sql = 'SELECT likes FROM blog_posts WHERE id = ?'
  const result = await query(sql, [postId]) as any[]
  if (!result || !result[0]) {
    return 0
  }
  return result[0].likes
}

// 添加文章
export async function addPost(post: Post): Promise<void> { 
  const sql = 'INSERT INTO blog_posts (title, description, content, categoryId, tags, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?)'
  
  // 将ISO格式转换为MySQL兼容的DATETIME格式
  const now = new Date()
  const mysqlDateTime = now.toISOString().slice(0, 19).replace('T', ' ')
  
  await query(sql, [
    post.title, 
    post.description, 
    post.content, 
    post.categoryId, 
    Array.isArray(post.tags) ? post.tags.join(',') : post.tags, 
    mysqlDateTime, 
    mysqlDateTime
  ])
}

// 修改文章
export async function updatePost(post: Post): Promise<void> { 
  const sql = 'UPDATE blog_posts SET title = ?, description = ?, content = ?, categoryId = ?, tags = ?, status = ?, updated_at = ? WHERE id = ?'
  
  // 将ISO格式转换为MySQL兼容的DATETIME格式
  const now = new Date()
  const mysqlDateTime = now.toISOString().slice(0, 19).replace('T', ' ')
  
  await query(sql, [
    post.title, 
    post.description, 
    post.content, 
    post.categoryId, 
    Array.isArray(post.tags) ? post.tags.join(',') : post.tags, 
    post.status, 
    mysqlDateTime, 
    post.id
  ])
}

// 删除文章
export async function deletePost(id: number): Promise<void> { 
  const sql = 'DELETE FROM blog_posts WHERE id = ?'
  await query(sql, [id])
}